using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.Sql.SchemaModel;
using Microsoft.Data.Schema.Sql;
using Microsoft.Data.Schema.StaticCodeAnalysis;
using System.Text.RegularExpressions;

using System.Linq;

namespace Neznayka
{
    /// <summary>
    /// This is a SQL rule which returns a warning message 
    /// whenever there is a ........................................
    /// This rule only applies to .................................
    /// </summary>

    [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
    [DataRuleAttribute(
        NeznaykaConstants.NameSpace,
        NeznaykaConstants.CheckUniqueKeysAreNotDuplicatedRuleId,
        NeznaykaConstants.ResourceBaseName,
        NeznaykaConstants.CheckUniqueKeysAreNotDuplicated_RuleName,
        NeznaykaConstants.CategoryDatabaseStructures,
        DescriptionResourceId = NeznaykaConstants.CheckUniqueKeysAreNotDuplicated_ProblemDescription)]
    [SupportedElementType(typeof(ISqlIndex))]
    [SupportedElementType(typeof(ISqlPrimaryKeyConstraint))]
    [SupportedElementType(typeof(ISqlUniqueConstraint))]
    public class CheckUniqueKeysAreNotDuplicatedRule : StaticCodeAnalysisRule
    {
        #region Overrides
        /// <summary>
        /// Analyze the model element
        /// </summary>
        public override IList<DataRuleProblem> Analyze(DataRuleSetting ruleSetting, DataRuleExecutionContext context)
        {

            // (Re)-Load Environment settings
            List<DataRuleProblem> problems;
            SqlSchemaModel sqlSchemaModel;
            ISqlModelElement sqlElement;
            TSqlFragment sqlFragment;
            DMVRuleSetup.RuleSetup(context, out problems, out sqlSchemaModel, out sqlElement, out sqlFragment);

            // Refresh cached index/constraints/tables lists from Model
            DMVRuleSetup.RefreshDDLCache(sqlSchemaModel);

            List<TSqlFragment> issues = new List<TSqlFragment>();

            ISqlIndex idx = sqlElement as ISqlIndex;
            ISqlPrimaryKeyConstraint pk = sqlElement as ISqlPrimaryKeyConstraint;
            ISqlUniqueConstraint uk = sqlElement as ISqlUniqueConstraint;

            String owningObjectSchema = null; //self.IndexedObject.Name.Parts[0];
            String owningObjectTable = null; //self.IndexedObject.Name.Parts[1];

            List<ISqlIndexedColumnSpecification> colSpec = null;
            String SourceName = null;
            int SourceOffSet = 0;
            int SourceLength = 0;

            bool unique = true;
            if (idx != null)
            {
                unique = idx.IsUnique;
                SourceName = idx.PrimarySource.SourceName;
                SourceOffSet = idx.PrimarySource.Offset;
                SourceLength = idx.PrimarySource.Length;

                
                owningObjectSchema = idx.IndexedObject.Name.Parts[0];
                owningObjectTable = idx.IndexedObject.Name.Parts[1];
                colSpec = idx.ColumnSpecifications.ToList();
            }
            else if (pk != null)
            {
                SourceName = pk.PrimarySource.SourceName;
                SourceOffSet = pk.PrimarySource.Offset;
                SourceLength = pk.PrimarySource.Length;

                owningObjectSchema = pk.DefiningTable.Name.Parts[0];
                owningObjectTable = pk.DefiningTable.Name.Parts[1];
                colSpec = pk.ColumnSpecifications.ToList();
            }
            else if (uk != null)
            {
                SourceName = uk.PrimarySource.SourceName;
                SourceOffSet = uk.PrimarySource.Offset;
                SourceLength = uk.PrimarySource.Length;

                owningObjectSchema = uk.DefiningTable.Name.Parts[0];
                owningObjectTable = uk.DefiningTable.Name.Parts[1];
                colSpec = uk.ColumnSpecifications.ToList();
            }


            if (unique && colSpec != null)
            {
                List<String> LeadingEdgeIndexColumns = new List<String>();

                {
                    String lastElement = "";
                    foreach (var c in colSpec)
                    {
                        foreach (var n in c.Column.Name.Parts)
                        {
                            lastElement = n;
                        }
                        LeadingEdgeIndexColumns.Add(lastElement);
                    }
                }


                List<ISqlPrimaryKeyConstraint> pks = ModelIndexAndKeysUtils.getPrimaryKeys(owningObjectSchema, owningObjectTable);
                List<ISqlIndex> indexes = ModelIndexAndKeysUtils.getIndexes(owningObjectSchema, owningObjectTable);
                List<ISqlUniqueConstraint> uniqueConstraints = ModelIndexAndKeysUtils.getUniqueConstraints(owningObjectSchema, owningObjectTable);

                bool foundMoreConciseUniqueCondition = false;
                foreach (var v in pks)
                {
                    if (v.PrimarySource.SourceName != SourceName || ( v.PrimarySource.Offset != SourceOffSet || v.PrimarySource.Length != SourceLength ) )  /// shit but it's all we have !!!
                    {
                        List<String> PKLeadingEdgeIndexColumns = new List<String>();
                        foreach (var c in v.ColumnSpecifications)
                        {
                            String lastElement = "";
                            foreach (var n in c.Column.Name.Parts)
                            {
                                lastElement = n;
                            }
                            PKLeadingEdgeIndexColumns.Add(lastElement);
                        }
                        foundMoreConciseUniqueCondition = determineIfThisConstraintIsImpliedByTheOtherConstraint(LeadingEdgeIndexColumns, PKLeadingEdgeIndexColumns);
                        if (foundMoreConciseUniqueCondition)
                        {
                            break;
                        }
                    }
                }
                if (!foundMoreConciseUniqueCondition)
                {
                    foreach (var v in indexes.Where( n => n.IsUnique).Select(n=>n) )
                    {
                        if (v.PrimarySource.SourceName != SourceName || (v.PrimarySource.Offset != SourceOffSet || v.PrimarySource.Length != SourceLength) )  /// shit but it's all we have !!!
                        {
                            List<String> OtherLeadingEdgeIndexColumns = new List<String>();
                            foreach (var c in v.ColumnSpecifications)
                            {
                                String lastElement = "";
                                foreach (var n in c.Column.Name.Parts)
                                {
                                    lastElement = n;
                                }
                                OtherLeadingEdgeIndexColumns.Add(lastElement);
                            }
                            foundMoreConciseUniqueCondition = determineIfThisConstraintIsImpliedByTheOtherConstraint(LeadingEdgeIndexColumns, OtherLeadingEdgeIndexColumns);
                            if (foundMoreConciseUniqueCondition)
                            {
                                break;
                            }
                        }
                    }

                }
                if (!foundMoreConciseUniqueCondition)
                {
                    foreach (var v in uniqueConstraints)
                    {
                        // if this 'index' isn't the index we're checking - check it.
                        if (v.PrimarySource.SourceName != SourceName || (v.PrimarySource.Offset != SourceOffSet || v.PrimarySource.Length != SourceLength))  /// shit but it's all we have !!!
                        {
                            List<String> ConstraintLeadingEdgeIndexColumns = new List<String>();
                            foreach (var c in v.ColumnSpecifications)
                            {
                                String lastElement = "";
                                foreach (var n in c.Column.Name.Parts)
                                {
                                    lastElement = n;
                                }
                                ConstraintLeadingEdgeIndexColumns.Add(lastElement);
                            }
                            foundMoreConciseUniqueCondition = determineIfThisConstraintIsImpliedByTheOtherConstraint(LeadingEdgeIndexColumns, ConstraintLeadingEdgeIndexColumns);
                            if (foundMoreConciseUniqueCondition)
                            {
                                break;
                            }
                        }
                    }
                }
                if (foundMoreConciseUniqueCondition)
                {
                    issues.Add(sqlFragment);
                }

                // Create problems for each object
                foreach (TSqlFragment issue in issues)
                {
                    DataRuleProblem problem = new DataRuleProblem(this,
                                                String.Format(CultureInfo.CurrentCulture, this.RuleProperties.Description, SqlRuleUtils.GetElementName(sqlSchemaModel, sqlElement)),
                                                sqlElement);

                    SqlRuleUtils.UpdateProblemPosition(problem, issue.StartOffset, issue.FragmentLength);
                    problems.Add(problem);
                }
            }
            return problems;
        }

        private static bool determineIfThisConstraintIsImpliedByTheOtherConstraint(List<String> TheseKeysColumns, List<String> TheOtherKeysColumns)
        {
            bool foundIndexThatMatchesAKey = false;

            List<Int32> allPos = ModelIndexAndKeysUtils.getCorrespondingKeyPositions(TheOtherKeysColumns, TheseKeysColumns);
            List<Int32> matchedPos = allPos.Where(n => n != -1).Select(n => n).ToList();

            if (TheseKeysColumns.Count >= TheOtherKeysColumns.Count
                && allPos.Count == matchedPos.Count
                && matchedPos.Count > 0
                )
            {
                foundIndexThatMatchesAKey = true;
            }

            return foundIndexThatMatchesAKey;
        }

        #endregion

    }
}
